package com.projectbox.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import com.projectbox.dao.AnnouncementDao;
import com.projectbox.model.Announcement;

public class JdbcAnnouncementDao implements AnnouncementDao{

	private Logger logger = LoggerFactory.getLogger(getClass());
	
	private SimpleJdbcTemplate simpleJdbcTemplate;
	private SimpleJdbcInsert insertTemplate;

	public void setDataSource(DataSource dataSource) {
		this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
		this.insertTemplate = new SimpleJdbcInsert(dataSource).withTableName(
				"announcements").usingGeneratedKeyColumns("id");
	}
	
	public List<Announcement> getAnnouncements() {
		String sql = "select * from announcements order by time_created desc";
		List<Announcement> announcements = simpleJdbcTemplate.query(sql,
				new AnnouncementMapper());

		return announcements;
	}

	public Announcement getAnnouncement(Long id) {
		String sql = "SELECT * FROM announcements WHERE id = ?";
		Announcement announcement = null;
		try {
			announcement = this.simpleJdbcTemplate.queryForObject(sql,
					new AnnouncementMapper(), id);
		} catch (Exception e) {
			announcement = null;
		}
		
		return announcement;
	}

	public void insert(Announcement announcement) {
		logger.debug("insert new announcement: " + announcement);

		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("title", announcement.getTitle());
		parameters.put("content", announcement.getContent());
		parameters.put("time_created", announcement.getTimeCreated());
		parameters.put("time_modified", announcement.getTimeModified());
		parameters.put("admin_id", announcement.getAdminId());

		Number id = insertTemplate.executeAndReturnKey(parameters);
		announcement.setId(id.longValue());
	}

	public void update(Announcement announcement) {
		String sql = "UPDATE announcements SET " +
					"title='" + announcement.getTitle() + "', " + 
					"content='" + announcement.getContent() + "', " +
					"time_created=" + announcement.getTimeCreated() + ", " +
					"time_modified=" + announcement.getTimeModified() + ", " +
					"admin_id=" + announcement.getAdminId() + " where id = ?";
		simpleJdbcTemplate.update(sql, announcement.getId());
	}

	public void delete(Long id) {
		String sql = "delete from announcements where id = ?";
		simpleJdbcTemplate.update(sql, id);
	}
	
	private static final class AnnouncementMapper implements RowMapper<Announcement> {
		public Announcement mapRow(ResultSet rs, int rowNum) throws SQLException {
			Announcement announcement = new Announcement();
			announcement.setId(rs.getLong("id"));
			announcement.setTitle(rs.getString("title"));
			announcement.setContent(rs.getString("content"));
			announcement.setTimeCreated(rs.getLong("time_created"));
			announcement.setTimeModified(rs.getLong("time_modified"));
			announcement.setAdminId(rs.getLong("admin_id"));
			return announcement;
		}
	}

}
